﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Dapper;
using Weight.Model;
using Weight.Base;
using Weight.Model.ParamEntity;
using Weight.Model.ResultEntity;
using Dapper.Contrib;
using Dapper.Contrib.Extensions;
using System.Data.SqlClient;

namespace Weight.Dal.Dispatch
{
    public class DalDispatch
    {
        /// <summary>
        /// 查询派车数据
        /// </summary>
        /// <param name="filterVal"></param>
        /// <returns></returns>
        public IEnumerable<DispatchCarEntity> Query(string filterVal)
        {
            string sql = " select * from DispatchCar where  state=@state ";
            string plateNum = string.Empty;
            if (!string.IsNullOrEmpty(filterVal))
            {
                plateNum = "%" + filterVal + "%";
                sql += " and PlateNumber like @plateNumber";
            }
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<DispatchCarEntity>(sql,new { state=0, plateNumber= plateNum });
            }
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public DispatchQueryRes List(QueryDispatchReq req)
        {
            int rowStart = req.PageSize * (req.PageIndex - 1);
            int rowEnd = req.PageSize * req.PageIndex;
            string sql = string.Empty;
            StringBuilder whereBuider = new StringBuilder(" where   IsDel=@IsDel ");
            if(req != null)
            {
                if(req.State != -1)
                {
                    whereBuider.Append(" and  State=@State"); 
                }

                if(!string.IsNullOrEmpty(req.StartDate) && !string.IsNullOrEmpty(req.EndDate))
                {
                    whereBuider.Append(" and (Convert(DateTime,CreateDate)>=Convert(DateTime,@StartDate)")
                        .Append(" and Convert(DateTime,CreateDate)<=Convert(DateTime,@EndDate)").Append(")");
                }

                if(!string.IsNullOrEmpty(req.PlateNumber))
                {
                    req.PlateNumber = "%" + req.PlateNumber + "%";
                    whereBuider.Append(" and PlateNumber like @PlateNumber ");
                }

                if(!string.IsNullOrEmpty(req.ContractNo))
                {
                    whereBuider.Append(" and ContractNo=@ContractNo ");
                }

                if(!string.IsNullOrEmpty(req.Material))
                {
                    req.Material = "%" + req.Material + "%";
                    whereBuider.Append(" and Material like @Material ");
                }

                if (!string.IsNullOrEmpty(req.Sender))
                {
                    req.Sender = "%" + req.Sender + "%";
                    whereBuider.Append(" and Sender like @Sender ");
                }

                if (!string.IsNullOrEmpty(req.Receiver))
                {
                    req.Receiver = "%" + req.Receiver + "%";
                    whereBuider.Append(" and Receiver like @Receiver ");
                }

                StringBuilder sqlBuider = new StringBuilder();
                sqlBuider.Append(" select *  from  ( select *,ROW_NUMBER() over(order by CreateDate) as rank from DispatchCar ")
                    .Append(whereBuider.ToString()).Append(")as tab ")
                    .Append(" where rank between " + (rowStart + 1) + " and " + rowEnd + "");
                sqlBuider.Append(" order by CreateDate desc ;select count(1) from DispatchCar ").Append(whereBuider.ToString());
                sql = sqlBuider.ToString();
            }

            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
               var res=   conn.QueryMultiple(sql, req);
                return  new DispatchQueryRes() {
                    DispatchList = res.Read<DispatchCarEntity>().ToList(),
                    Total = res.Read<int>().FirstOrDefault()
                };
            }
        }


        /// <summary>
        /// 获取选择列表
        /// </summary>
        /// <param name="plateNum">车号</param>
        /// <param name="customer">客户名称</param>
        /// <param name="material">物料名称</param>
        /// <returns></returns>
        public IEnumerable<DispatchCarEntity> ListSelect(string plateNum, string customer, string material)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @"select * from  DispatchCar
                    where IsDel='否' and State=0 ";
                if (!string.IsNullOrEmpty(plateNum))
                {
                    plateNum = "%" + plateNum + "%";
                    sql += " and PlateNumber like @PlateNumber";
                }
                if (!string.IsNullOrEmpty(customer))
                {
                    customer = "%" + customer + "%";
                    sql += " and Customer like @Customer";
                }
                if (!string.IsNullOrEmpty(material))
                {
                    material = "%" + material + "%";
                    sql += " and Material like @Material";
                }

                return conn.Query<DispatchCarEntity>(sql, new { PlateNumber=plateNum, Customer= customer, Material=material });
            }
        }

        /// <summary>
        /// 根据Id 获取派车
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DispatchCarEntity GetById(string id)
        {
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                return  conn.Get<DispatchCarEntity>(id);
            }
        }

        /// <summary>
        /// 根据车号获取派车
        /// </summary>
        /// <param name="plateNum">车号</param>
        /// <returns></returns>
        public DispatchCarEntity GetByPlateNum(string plateNum)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select * from DispatchCar where  PlateNumber=@plateNum and State=@state and IsDel=isDel";
                return conn.Query<DispatchCarEntity>(sql, new { plateNum,state=0, isDel="否" })?.FirstOrDefault();
            }
        }

        /// <summary>
        /// 派车是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsExist(string id)
        {
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select count(1) from DispatchCar where Id=@Id";
                var count = conn.Query<int>(sql, new { Id = id }).FirstOrDefault();
                return count > 0 ? true : false;
            }
        }

        /// <summary>
        /// 车号是否有派车信息
        /// </summary>
        /// <param name="plateNum">车号</param>
        /// <returns></returns>
        public bool PlateNumIsExist(string plateNum)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select count(1) from DispatchCar where  PlateNumber=@plateNum and State=@state and IsDel=isDel";
                var count = conn.Query<int>(sql, new { plateNum, state = 0, isDel = "否" })?.FirstOrDefault();
                return count > 0 ? true : false;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update(DispatchCarEntity entity)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Update(entity);
            }
        }

       /// <summary>
       /// 添加
       /// </summary>
       /// <param name="entity"></param>
       /// <returns></returns>
        public bool Insert(DispatchCarEntity entity)
        {
            using(IDbConnection  conn =SessionFactory.CreateConnection())
            {
                 conn.Insert(entity);
                 return true;
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool delete(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "delete from  DispatchCar where Id=@Id";
                conn.Execute(sql, new { Id = id });
                return true;
            }
        }
    }
}
